<?php
declare (strict_types = 1);

namespace app\command\logic;

use app\command\logic\BaseLogic;
use think\console\Output;

/**
 * 统计-社区数据
 * php think cli stats_community --msg='{"days":30}'
 */
class CliStatsCommunityLogic extends BaseLogic implements CliInterface
{

    /**
     * 入口方法
     * {@inheritdoc}
     */
    public function run(Output $output, array $msg): bool
    {
        $date = isset($msg['date']) ? ($msg['date'] == 'today' ? date('Y-m-d') : $msg['date']) : null;
        if ( ! empty($date)) {
            $insertData[] = self::dataForCommunity($date);
        } else {
            // var_dump($msg);exit;
            $insertData = [];
            $days       = intval($msg['days'] ?? 1);
            for ($i = 1; $i <= $days; $i++) {
                $date = date('Y-m-d', strtotime("-$i days"));
                // echo $date . "\n";exit;
                $insertData[] = self::dataForCommunity($date);
            }
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_community')
                ->replace()
                ->insertAll($insertData);
        }
        return true;
    }

    private static function dataForCommunity($date)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // sign_status 1未使用 2入驻已使用 3已返还到余额
        $field = [
            'COALESCE(count(distinct user_id), 0) AS signin_count',
            'COALESCE(count(CASE WHEN sign_status IN (2,3) THEN 1 ELSE 0 END), 0) AS real_signin_count',
        ];
        $data = \think\facade\Db::table('community_sign_user')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        // 社区数总数
        $field2 = [
            'count(*) as community_total', // 社区数总数
            'COALESCE(sum(CASE WHEN type = 0 THEN 1 ELSE 0 END), 0) AS square_total', // title: '社区广场'
            'COALESCE(sum(CASE WHEN type = 1 THEN 1 ELSE 0 END), 0) AS new_count', // title: '新人场'
            'COALESCE(sum(CASE WHEN type = 2 THEN 1 ELSE 0 END), 0) AS activity_count', // title: '活动场'
        ];
        $data2 = \think\facade\Db::table('community_order')
            ->field($field2)
        // 2024-09-09 13:42:00 这不需要添加  last_order_id = 的条件
        // ->where('last_order_id', 0) // 上次id 为0 排除重复社区
            ->where('status', 2) // 1未支付 2已支付 3取消
        // ->where('create_at', '<=', $end)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('community_order')->getlastsql();exit;
        $field3 = [
            'count(distinct user_id) as participant_count', // 参与人数
            // 'COALESCE(count(CASE WHEN status = 2 THEN (distinct user_id) ELSE 0 END), 0) AS real_participant_count', // title: '实际参与人数'
            'COALESCE(COUNT(DISTINCT CASE WHEN status = 2 THEN user_id END), 0) AS real_participant_count',
            'COALESCE(sum(pay_price), 0) AS sand_pay_count', // title: '电子钱包'
            'COALESCE(sum(premium), 0) AS all_revenue', // 总溢价
        ];
        $data3 = \think\facade\Db::table('community_order')
            ->field($field3)
            ->where('status', 2) // 1未支付 2已支付 3取消
            ->whereBetweenTime('create_at', $date, $end)
            ->find();

        //   `pay_status` tinyint(1) DEFAULT '1' COMMENT '1微信 2支付宝 3余额 4杉德',
        $field4 = [
            'COALESCE(sum(community_pay_amount+pay_price), 0) AS order_price_count', // title: '订单总金额'
            'COALESCE(SUM(CASE WHEN pay_status = 2 THEN pay_price ELSE 0 END), 0) AS alipay_count',
            'COALESCE(SUM(CASE WHEN pay_status = 3 THEN community_pay_amount ELSE 0 END), 0) AS community_pay_amount',
            'COALESCE(SUM(CASE WHEN order_status = 4 THEN (community_pay_amount+pay_price) ELSE 0 END), 0) AS all_fee',

            'COALESCE(sum(sign_price), 0) AS sign_price', // 红包抵扣
        ];
        // order_status 0线上商城单 1社区单 2签到单 3礼包单 4转让单 5供应链单 6线下订单 7话费单 8充值
        $data4 = \think\facade\Db::table('order_pay')
            ->field($field4)
            ->where('order_status', 'in', [1, 4])
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('community_op_order')->getlastsql();exit;

        // 总波比 奖励出去的金额
        // 平台收益 = 总溢价 - 总波比
        $field5 = [
            // 'COALESCE(sum(amount_price), 0) AS sys_revenue', // 平台收益 TODO
            'COALESCE(sum(amount), 0) AS all_rate', // 总波比
        ];
        $data5 = \think\facade\Db::table('member_community_amount_log')
            ->field($field5)
            ->where('type', 0) // 积分类型 0社区绿色积分 1礼包绿色积分 2推广绿色积分 3分红绿色积分
            ->where('status', 1) // 1加 2减少
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        // echo \think\facade\Db::table('member_community_amount_log')->getlastsql();exit;

        // 签到页浏览数
        $data['signin_view_count'] = \think\facade\Db::table('member_oplog')
        // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->where('api', '/api/community/signInfo')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 社区广场浏览数
        $data['square_view_count'] = \think\facade\Db::table('member_oplog')
        // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->where('api', '/api/community/communityPayList')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 社区点击竞拍数
        $data['community_click_count'] = \think\facade\Db::table('member_oplog')
        // ->whereIn('api', ['/api/goods/goodsDetail', '/api/supply/goodsDetail'])
            ->where('api', '/api/community/click')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();

        // 平台收益 = 总溢价 - 总波比 - 红包抵扣
        $data['sys_revenue'] = $data3['all_revenue'] - $data5['all_rate'] - $data4['sign_price'];

        // 收益百分比（平台收益/总溢价
        $data['revenue_rate'] = $data3['all_revenue'] != 0 ? $data['sys_revenue'] / $data3['all_revenue'] : 0;
        $data['date']         = $date;
        // var_dump($data);exit;
        return array_merge($data, $data2, $data3, $data4, $data5);
    }
}
